package DBLayer;

import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

/**
 * DBWorksOn.java
 * @author 
 * @version 
 */

public class DBSalesLineItem {
    private  Connection con;
    /** Creates a new instance of DBWorksOn */
    public DBSalesLineItem() {
        con = DbConnection1.getInstance().getDBcon();
    }
    
    public ArrayList<SalesLineItem> findAllSalesLineItem(int salesID)
    {
        ArrayList<SalesLineItem> salLineObjects = new ArrayList<SalesLineItem>();
        salLineObjects = miscWhere("salID = '" + salesID + "'",true);
        return salLineObjects;
    }
    
    public SalesLineItem findSalesLineItem(int salesID, boolean retriveAssociation)
    {
    	String wClause = "salID = '" + salesID + "'";
		return singleWhere(wClause, retriveAssociation);
        
    }
    
    private SalesLineItem singleWhere(String wClause, boolean retriveAssociation) {
    	
    	ResultSet results;
    	SalesLineItem sliObj = new SalesLineItem();
    	
    	String query = buildQuery(wClause);
    	
    	System.out.println(query);
    	
    	try{
    		Statement stmt = con.createStatement();
    		stmt.setQueryTimeout(5);
    		results = stmt.executeQuery(query);
    		
    		if(results.next()){
    			
    			sliObj = buildSalesLineItem(results);
    			stmt.close();
    			
    			if(retriveAssociation)
    			{
    				DBSalesOrder dbSalesOrder = new DBSalesOrder();
    				SalesOrder so = dbSalesOrder.findSalesOrder(sliObj.getSalLineID(), false);
    				sliObj.setSalLineID(so.getSalesID());
    				System.out.println("saleslineitem is selected");
    			}
    			
    		}
    			
    		
    	}
    		catch(Exception e){
    			System.out.println("Query exception:"+e);
    		}
    		return sliObj;
    	

    }
    
    private ArrayList<SalesLineItem> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<SalesLineItem> list = new ArrayList<SalesLineItem>();	
		
		String query =  buildQuery(wClause);
                System.out.println("DBWorkson" + query);  
  		try{ // read from SalesLineItem
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		 System.out.println("DBWorkson 2" );  

			while( results.next() ){
				SalesLineItem sliObj = new SalesLineItem();
				sliObj = buildSalesLineItem(results);	
                         list.add(sliObj);	
			}//end while
			stmt.close();
                        if(retrieveAssociation)// for each SalesLineItem object find the product name
                        {
                            DBProduct dbProduct = new DBProduct();
                            for(SalesLineItem sliObj : list){
                               int productID = sliObj.getProduct().getProductId();
                               Product prodObj = dbProduct.findProduct(productID, retrieveAssociation);
                               sliObj.setProduct(prodObj);
                            }
                            
                        }
                        
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    private SalesLineItem buildSalesLineItem(ResultSet results)
    {
    	SalesLineItem sliObj = new SalesLineItem();
        SalesOrder salesOrder = new SalesOrder();
        Product product = new Product();
        sliObj.setSalesOrder(salesOrder); //make reference to salesOrder
        sliObj.setProduct(product); //make reference to product
         try{
        	 sliObj.setSalLineID(results.getInt("salLineID"));
        	 salesOrder.setSalesID(results.getInt("salesID"));
        	 product.setProductId(results.getInt("productId"));
        	 sliObj.setQuantity(results.getInt("quantity"));
          }
         catch(Exception e)
         {
             System.out.println("building workson object");
         }
        
        return sliObj;
        
    }
    //method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM SalesLineItem";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
}
